<?php
/**
 * Class that operate on table 'roles'. Database Mysql.
 *
 * @author: http://phpdao.com
 * @date: 2015-05-26 14:36
 */
class RolesMySqlDAO implements RolesDAO{

	/**
	 * Get Domain object by primry key
	 *
	 * @param String $id primary key
	 * @return RolesMySql 
	 */
	public function load($id){
		$sql = 'SELECT * FROM roles WHERE role_id = ?';
		$sqlQuery = new SqlQuery($sql);
		$sqlQuery->setNumber($id);
		return $this->getRow($sqlQuery);
	}

	/**
	 * Get all records from table
	 */
	public function queryAll(){
		$sql = 'SELECT * FROM roles';
		$sqlQuery = new SqlQuery($sql);
		return $this->getList($sqlQuery);
	}
	
	/**
	 * Get all records from table ordered by field
	 *
	 * @param $orderColumn column name
	 */
	public function queryAllOrderBy($orderColumn){
		$sql = 'SELECT * FROM roles ORDER BY '.$orderColumn;
		$sqlQuery = new SqlQuery($sql);
		return $this->getList($sqlQuery);
	}
	
	/**
 	 * Delete record from table
 	 * @param role primary key
 	 */
	public function delete($role_id){
		$sql = 'DELETE FROM roles WHERE role_id = ?';
		$sqlQuery = new SqlQuery($sql);
		$sqlQuery->setNumber($role_id);
		return $this->executeUpdate($sqlQuery);
	}
	
	/**
 	 * Insert record to table
 	 *
 	 * @param RolesMySql role
 	 */
	public function insert($role){
		//print_r("hello");exit;
		$sql = 'INSERT INTO roles (first_name, created_date, updated_date) VALUES ("'.$role->first_name.'", "'.$role->created_date.'", "'.$role->updated_date.'")';
		$sqlQuery = new SqlQuery($sql);
		//print_r($sql);exit;

		$id = $this->executeInsert($sqlQuery);	
		$role->role_id = $id;
		return $id;
	}
	
	public function insert_modules($role){
		//print_r("hello");exit;
		$sql = 'insert into role_modules (role_id,module_id,created_date,updated_date) VALUES ("'.$role->role_id.'","'.$role->module_id.'","'.$role->created_date.'","'.$role->updated_date.'")';
		//print_r($sql);exit;
		$sqlQuery = new SqlQuery($sql);
		//print_r($sql);exit;

		$id = $this->executeInsert($sqlQuery);	
		$role->role_id = $id;
		return $id;
	}
	/**
 	 * Update record in table
 	 *
 	 * @param RolesMySql role
 	 */
	public function update($role){
		$sql = 'UPDATE roles SET first_name = ?,updated_date = ? WHERE role_id = ?';
		$sqlQuery = new SqlQuery($sql);
		$sqlQuery->set($role-> first_name);
		$sqlQuery->set($role-> updated_date);
		$sqlQuery->setNumber($role->role_id);
		return $this->executeUpdate($sqlQuery);
	}
	
	public function delete_role_modules($role){
		$sql = 'delete from role_modules where role_id="'.$role->role_id.'"';
		$sqlQuery = new SqlQuery($sql);
		return $this->executeUpdate($sqlQuery);
	}
	
	public function insert_role_modules($role){
		$sql = "insert into `role_modules` (role_id,module_id,created_date,updated_date) VALUES ('".$role->role_id."','".$role->module_id ."','".date("Y-m-d H:i:s")."','".date("Y-m-d H:i:s")."')";
		$sqlQuery = new SqlQuery($sql);
		return $this->executeUpdate($sqlQuery);
	}

	/**
 	 * Get Record from table
 	 */
	 
	 public function get_role($role){
		$sql = "select * from`roles` where role_id='".$role->role_id."'";
		$sqlQuery = new SqlQuery($sql);
		return $this->getRow($sqlQuery);
	}
	
	 public function get_role_modules($role){
		$sql = "SELECT module_id FROM role_modules WHERE role_id='".$role->role_id."'";
		$sqlQuery = new SqlQuery($sql);
		
		return $this->getList($sqlQuery);
	}
	
	 public function get_rolemodules_list(){
		$sql = "select module_id,module_name from modules where parent_module_id > '0'";
		$sqlQuery = new SqlQuery($sql);
		
		return $this->getList($sqlQuery);
	}
	 /**
 	 * Delete Role
 	 */
	
	 public function delete_role($role){
		$sql = "UPDATE roles LEFT JOIN role_modules ON roles.role_id=role_modules.role_id
	SET roles.status='".$role->status."',role_modules.status='".$role->status."' WHERE roles.role_id='".$role->role_id."'";
		$sqlQuery = new SqlQuery($sql);
		//print_r($sqlQuery);exit;
		return $this->executeUpdate($sqlQuery);
	}
	 
	 /**
 	 * All Roles
 	 */
	 
	  public function all_roles($role){
		$sql = "select * from roles order by ".$role->order_by." ".$role->sort_order." limit ".$role->results_per_page." offset ".$role->offset;
		$sqlQuery = new SqlQuery($sql);
		//print_r($sqlQuery);exit;
		return $this->getList($sqlQuery);
	} 
	
	  public function total_roles(){
		$sql = "SELECT count(*) as total from roles";
		$sqlQuery = new SqlQuery($sql);
		//print_r($sqlQuery);exit;
		return $this->querySingleResult($sqlQuery);
	}
	
	 /**
 	 * Delete all rows
 	 */
	public function clean(){
		$sql = 'DELETE FROM roles';
		$sqlQuery = new SqlQuery($sql);
		return $this->executeUpdate($sqlQuery);
	}

	public function queryByFirstName($value){
		$sql = 'SELECT * FROM roles WHERE first_name = ?';
		$sqlQuery = new SqlQuery($sql);
		$sqlQuery->set($value);
		return $this->getList($sqlQuery);
	}

	public function queryByStatus($value){
		$sql = 'SELECT * FROM roles WHERE status = ?';
		$sqlQuery = new SqlQuery($sql);
		$sqlQuery->setNumber($value);
		return $this->getList($sqlQuery);
	}

	public function queryByCreatedDate($value){
		$sql = 'SELECT * FROM roles WHERE created_date = ?';
		$sqlQuery = new SqlQuery($sql);
		$sqlQuery->set($value);
		return $this->getList($sqlQuery);
	}

	public function queryByUpdatedDate($value){
		$sql = 'SELECT * FROM roles WHERE updated_date = ?';
		$sqlQuery = new SqlQuery($sql);
		$sqlQuery->set($value);
		return $this->getList($sqlQuery);
	}


	public function deleteByFirstName($value){
		$sql = 'DELETE FROM roles WHERE first_name = ?';
		$sqlQuery = new SqlQuery($sql);
		$sqlQuery->set($value);
		return $this->executeUpdate($sqlQuery);
	}

	public function deleteByStatus($value){
		$sql = 'DELETE FROM roles WHERE status = ?';
		$sqlQuery = new SqlQuery($sql);
		$sqlQuery->setNumber($value);
		return $this->executeUpdate($sqlQuery);
	}

	public function deleteByCreatedDate($value){
		$sql = 'DELETE FROM roles WHERE created_date = ?';
		$sqlQuery = new SqlQuery($sql);
		$sqlQuery->set($value);
		return $this->executeUpdate($sqlQuery);
	}

	public function deleteByUpdatedDate($value){
		$sql = 'DELETE FROM roles WHERE updated_date = ?';
		$sqlQuery = new SqlQuery($sql);
		$sqlQuery->set($value);
		return $this->executeUpdate($sqlQuery);
	}


	
	/**
	 * Read row
	 *
	 * @return RolesMySql 
	 */
	protected function readRow($row){
		$role = new Role();
		
		$role->role_id = $row['role_id'];
		$role->module_id = $row['module_id'];
		$role->status = $row['status'];
		$role->module_name = $row['module_name'];
		$role->first_name = $row['first_name'];
		$role->created_date = $row['created_date'];
		$role->updated_date = $row['updated_date'];
		$role->order_by = $row['order_by'];
		$role->sort_order = $row['sort_order'];
		$role->results_per_page = $row['results_per_page'];
		$role->offset = $row['offset'];

		return $role;
	}
	
	protected function getList($sqlQuery){
		$tab = QueryExecutor::execute($sqlQuery);
		$ret = array();
		for($i=0;$i<count($tab);$i++){
			$ret[$i] = $this->readRow($tab[$i]);
		}
		return $ret;
	}
	
	/**
	 * Get row
	 *
	 * @return RolesMySql 
	 */
	protected function getRow($sqlQuery){
		$tab = QueryExecutor::execute($sqlQuery);
		if(count($tab)==0){
			return null;
		}
		return $this->readRow($tab[0]);		
	}
	
	/**
	 * Execute sql query
	 */
	protected function execute($sqlQuery){
		return QueryExecutor::execute($sqlQuery);
	}
	
		
	/**
	 * Execute sql query
	 */
	protected function executeUpdate($sqlQuery){
		return QueryExecutor::executeUpdate($sqlQuery);
	}

	/**
	 * Query for one row and one column
	 */
	protected function querySingleResult($sqlQuery){
		return QueryExecutor::queryForString($sqlQuery);
	}

	/**
	 * Insert row to table
	 */
	protected function executeInsert($sqlQuery){
		return QueryExecutor::executeInsert($sqlQuery);
	}
}
?>